<?php
	//filename : bud_view_mng.php
	require_once 'bud_mng'.EXT;
	require_once 'iam_xls'.EXT;

	class Bud_view_mng extends Bud_mng  {
		function view_report()
        {
			
			if($this->role=='yayasan' || $this->role=='admin' || $this->role=='manager'){
				$content = array ();
				$content['element'] = array ();
				
				$this->db->select_sum('total');
				$this->db->where('key_bud_m_version',$this->versi);
				$this->db->from('bud_m_d_activity');
				$kpu_total = $this->db->get()->row_array();
				$content['total'] = $kpu_total['total'];

				$this->db->from('bud_m_kpu'); 
				$query_kpu = $this->db->get();
				if ($query_kpu->num_rows() > 0)
				{
					foreach($query_kpu->result() as $rowkpu)
					{
						$this->db->select_sum('total');
						$this->db->where('LOCATE(\''.$rowkpu->key.'\',`key`)','1');
						$this->db->where('key_bud_m_version',$this->versi);
						$this->db->from('bud_m_d_activity');
						$pu_total = $this->db->get()->row_array();

						$this->db->where('key_bud_m_kpu',$rowkpu->key);
						$this->db->from('bud_m_pu');
						$query_pu = $this->db->get();
						
						if ($query_pu->num_rows()>0)
						{
							foreach($query_pu->result() as $rowpu)
							{
								$this->db->select_sum('total');
								$this->db->where('LOCATE(\''.$rowpu->key.'\',`key`) ','1');
								$this->db->where('key_bud_m_version',$this->versi);
								$this->db->from('bud_m_d_activity');
								$prog_total = $this->db->get()->row_array();

								$this->db->where('key_bud_m_pu',$rowpu->key);
								$this->db->from('bud_m_program');
								$query_prog = $this->db->get();
								
								if ($query_prog->num_rows()>0)
								{
									foreach($query_prog->result() as $rowprog)
									{
										$this->db->select_sum('total');
										$this->db->where('LOCATE(\''.$rowprog->key.'\',`key`) ','1');
										$this->db->where('key_bud_m_version',$this->versi);
										$this->db->from('bud_m_d_activity');
										$act_total = $this->db->get()->row_array();

										$this->db->where('key_bud_m_program',$rowprog->key);
										$this->db->where('key_bud_m_version',$this->versi);
										$this->db->from('bud_m_d_activity');
										$query_act = $this->db->get();
										if ($query_act->num_rows()>0)
										{
											
											foreach($query_act->result() as $rowact)
											{
												$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['element'][$rowprog->key.' '.$rowprog->name_program]['element'][$rowact->key.' '.$rowact->name_activity]=$rowact;
												$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['total'] = $pu_total['total'];
												$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['total'] = $prog_total['total'];
												$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['element'][$rowprog->key.' '.$rowprog->name_program]['total'] = $act_total['total'];
											}
										}else{
											$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['element'][$rowprog->key.' '.$rowprog->name_program]['element']='';
											
										}
									}
								}else{
									$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['element']='';
								}
							}
						}else{
							$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element']='';
						}
					}
				}
				
				if($this->role=='manager'){
					$nav = 'bud/navigation/administrator/nav_manager';
				}else if($this->role=='admin'){
					$nav = 'bud/navigation/administrator/navigation';
				}else{
					$nav = 'bud/navigation/administrator/nav_yayasan';
				}

				$data = array(
					'application' => $prefix,
					'application_title' => $this->common->get_application_description($prefix),
					'page' => 'View_Report',
					'navigation' => $this->load->view('nav/default', '', true).$this->load->view($nav, '', true),
					'content' => $this->load->view('bud/view_report',array('versi'=>$this->versi,'menus' => $menus,'content' => $content,'base' => $this->config->item('base_url').'system/application/modules/'),true),
				);
				$this->load->view('bud/layout/default', $data);
				

			}else if(strpos($this->role,'koordinator')==0){
				//SELECT * FROM `bud_m_d_activity` WHERE `key_bud_m_program` in (SELECT `key` FROM bud_m_program WHERE `key_bud_m_role`='koordinator_akademik')
				
				$this->db->select_sum('total');
				$this->db->where('key_bud_m_version',$this->versi);
				$this->db->where('key_bud_m_program in (SELECT `key` FROM bud_m_program WHERE `key_bud_m_role`=\''.$this->role.'\' )');
				$this->db->from('bud_m_d_activity');
				$kpu_total = $this->db->get()->row_array();
				$content['total'] = $kpu_total['total'];

				$this->db->from('bud_m_kpu'); 
				$query_kpu = $this->db->get();
				if ($query_kpu->num_rows() > 0)
				{
					foreach($query_kpu->result() as $rowkpu)
					{
						$this->db->select_sum('total');
						$this->db->where('LOCATE(\''.$rowkpu->key.'\',`key`)','1');
						$this->db->where('key_bud_m_version',$this->versi);
						$this->db->where('key_bud_m_program in (SELECT `key` FROM bud_m_program WHERE `key_bud_m_role`=\''.$this->role.'\' )');
						$this->db->from('bud_m_d_activity');
						$pu_total = $this->db->get()->row_array();

						$this->db->where('key_bud_m_kpu',$rowkpu->key);
						$this->db->from('bud_m_pu');
						$query_pu = $this->db->get();
						
						if ($query_pu->num_rows()>0)
						{
							foreach($query_pu->result() as $rowpu)
							{
								$this->db->select_sum('total');
								$this->db->where('LOCATE(\''.$rowpu->key.'\',`key`) ','1');
								$this->db->where('key_bud_m_version',$this->versi);
								$this->db->where('key_bud_m_program in (SELECT `key` FROM bud_m_program WHERE `key_bud_m_role`=\''.$this->role.'\' )');
								$this->db->from('bud_m_d_activity');
								$prog_total = $this->db->get()->row_array();

								$this->db->where('key_bud_m_pu',$rowpu->key);
								$this->db->where('`key_bud_m_role` = \''.$this->role.'\'');
								$this->db->from('bud_m_program');
								$query_prog = $this->db->get();
								
								if ($query_prog->num_rows()>0)
								{
									foreach($query_prog->result() as $rowprog)
									{
										$this->db->select_sum('total');
										$this->db->where('LOCATE(\''.$rowprog->key.'\',`key`) ','1');
										$this->db->where('key_bud_m_version',$this->versi);
										$this->db->where('key_bud_m_program in (SELECT `key` FROM bud_m_program WHERE `key_bud_m_role`=\''.$this->role.'\' )');
										$this->db->from('bud_m_d_activity');
										$act_total = $this->db->get()->row_array();

										$this->db->where('key_bud_m_program',$rowprog->key);
										$this->db->where('key_bud_m_version',$this->versi);
										$this->db->where('key_bud_m_program in (SELECT `key` FROM bud_m_program WHERE `key_bud_m_role`=\''.$this->role.'\' )');
										$this->db->from('bud_m_d_activity');
										$query_act = $this->db->get();
										if ($query_act->num_rows()>0)
										{
											
											foreach($query_act->result() as $rowact)
											{
												$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['element'][$rowprog->key.' '.$rowprog->name_program]['element'][$rowact->key.' '.$rowact->name_activity]=$rowact;
												$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['total'] = $pu_total['total'];
												$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['total'] = $prog_total['total'];
												$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['element'][$rowprog->key.' '.$rowprog->name_program]['total'] = $act_total['total'];
											}
										}else{
											$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['element'][$rowprog->key.' '.$rowprog->name_program]['element']='';
											
										}
									}
								}else{
									$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element'][$rowpu->key.' '.$rowpu->name_pu]['element']='';
								}
							}
						}else{
							$content['element'][$rowkpu->key.' '.$rowkpu->name_kpu]['element']='';
						}
					}
				}
				
				$data = array(
					'application' => $prefix,
					'application_title' => $this->common->get_application_description($prefix),
					'page' => 'View_Report',
					'navigation' => $this->load->view('nav/default', '', true).$this->load->view('bud/navigation/administrator/nav_penanggungjawab', '', true),
					'content' => $this->load->view('bud/view_report',array('versi'=>$this->versi,'menus' => $menus,'content' => $content,'base' => $this->config->item('base_url').'system/application/modules/'),true),
				);
				$this->load->view('bud/layout/default', $data);

			}else{
				$data = array(
					'application' => $prefix,
					'application_title' => $this->common->get_application_description($prefix),
					'page' => 'View_Report',
					'navigation' => $this->load->view('nav/default', '', true),
					'content' => $this->load->view('bud/illegaluser',array(),true),
				);
				$this->load->view('bud/layout/default', $data);
			}

            
        }


		function view_budgetlist()
		{
			//$query = "SELECT kpu.`key` as 'KPU', pu.`key` as 'PU', prg.`key` as 'Program', act.`key` as 'Activity', act.`name_activity` as 'Nama', act.`desc_activity` as 'Deskripsi', act.`januari`, act.`februari`, act.`maret`, act.`april`, act.`mei`, act.`juni`, act.`juli`, act.`agustus`, act.`september`, act.`oktober`, act.`september`, act.`november`, act.`desember`, act.`total` as Total, act.`gaji_honor` as 'Gaji/honor', act.`barang_tak_habis` as 'Barang tak habis', act.`barang_habis` as 'Barang habis', act.`perjalanan` as Perjalanan, act.`jasa_honor_ext` as 'Jasa/Honor/ext', act.`volume` as 'Volume', act.`operational` as 'Operational',  act.`key_bud_m_yayasan_activity` as 'Id yayasan', act.`key_bud_r_sumber` as 'Id sumber', act.`key_bud_r_unit` as 'Id unit' FROM bud_m_kpu kpu INNER JOIN bud_m_pu pu ON kpu.`key` = pu.`key_bud_m_kpu` INNER JOIN bud_m_program prg ON pu.`key` = prg.`key_bud_m_pu` INNER JOIN bud_m_d_activity act ON prg.`key` = act.`key_bud_m_program` ORDER BY kpu.`key` , pu.`key`, prg.`key`, act.`key`";
			
			$content = array();
			
			//----------------------------------------- option year data
			$option = array();
			$option['option_year'] = array();

			$query = $this->db->query("SELECT distinct(`key_bud_m_version`) as 'key' FROM `bud_m_d_activity` order by `key_bud_m_version` desc");
			foreach ($query->result_array() as $row){
				$option['option_year'][substr($row['key'],-4)] =substr($row['key'],-4);
			};
			//---------------------------------------------------  Version filter
			
			if(isset($_POST['year']) && trim($_POST['year'])!=NULL){
				$query = $this->db->query("SELECT `id`,`key` FROM bud_m_version WHERE `key` in (SELECT distinct(`key_bud_m_version`) FROM `bud_m_d_activity`) AND RIGHT(`key`,4)='".$_POST['year']."' order by `key` desc");
			}else{
				$query = $this->db->query("SELECT `id`,`key` FROM bud_m_version WHERE `key` in (SELECT distinct(`key_bud_m_version`) FROM `bud_m_d_activity`) AND RIGHT(`key`,4)='".date('Y',strtotime(now))."' order by `key` desc");
			}
			
			foreach ($query->result_array() as $row){
				$content['bud/bud_view_mng/download/'.$row['id']] = $row['key'];
			};
			//------------------------------------
			
			$menus = array(
				'Daftar budget' => array(),
			); 


			$menus['Daftar budget'] = array_merge($menus['Daftar budget'],$content);
			
			if($this->role=='admin'){
				$nav = $this->load->view('bud/navigation/administrator/navigation', '', true);
				$view = 'bud/view_budgetfile';
			}else if($this->role=='manager'){
				$nav = $this->load->view('bud/navigation/administrator/nav_manager', '', true);
				$view = 'bud/view_budgetfile';
			}else if($this->role=='yayasan'){
				$nav = $this->load->view('bud/navigation/administrator/nav_yayasan', '', true);
				$view = 'bud/view_budgetfile';
			}else if(strpos($this->role,'koordinator')==0){
				$nav = $this->load->view('bud/navigation/administrator/nav_penanggungjawab', '', true);
				$view = 'bud/view_budgetfile';
			}else{
				$nav = '';
				$view = 'bud/illegaluser';
			}

			$data = array(
				'application' => $prefix,
				'application_title' => $this->common->get_application_description($prefix),
				'page' => 'View_Budgetlist',
				'navigation' => $this->load->view('nav/default', '', true).$nav,
				'content' => $this->load->view($view,array('menus'=>$menus,'content' => $content,'base' => $this->config->item('base_url').'system/application/modules/','option'=>$option),true),
			);

			$this->load->view('bud/layout/default', $data);
		}

		function download(){
			$record =  $this->db->query("SELECT `key` from bud_m_version where `id`='".$this->uri->segment(4)."'");
			$row = $record->row_array(1);
			$versi = $row['key'];
			

			$query = "SELECT SUBSTRING(`key`,1,1) as 'KPU', SUBSTRING(`key`,3,2) as 'PU', SUBSTRING(`key`,6,2) as 'Program', SUBSTRING(`key`,9,2) as 'Activity', `name_activity` as 'Nama', `desc_activity` as 'Deskripsi', `januari`, `februari`, `maret`, `april`, `mei`, `juni`, `juli`, `agustus`, `september`, `oktober`, `september`, `november`, `desember`, `total` as Total, `gaji_honor` as 'Gaji/honor', `barang_tak_habis` as 'Barang tak habis', `barang_habis` as 'Barang habis', `perjalanan` as Perjalanan, `jasa_honor_ext` as 'Jasa/Honor/ext', `volume` as 'Volume', `operational` as 'Operational',  `key_bud_m_yayasan_activity` as 'Id yayasan', `key_bud_r_sumber` as 'Id sumber', `key_bud_r_unit` as 'Id unit' FROM bud_m_d_activity WHERE `key_bud_m_version`= '$versi' ORDER BY 'KPU' , 'PU' , 'Program', 'Activity'";

			$xls = new IAM_XLS('budget_'.$versi);
			
			$xls->WriteValue(0,0,'Data Budgetting');
			$xls->WriteValue(1,0,"Versi : $versi");

			$xls->WriteSQLDump($query, $this->db->database, $this->db->username, $this->db->password, $this->db->hostname);

			$xls->OutputFile();
		}
	}
?>